﻿using System;
using System.Collections.Generic;
using System.Text;
using System.Data;
using System.Data.Common;
using System.Data.SqlClient;
using System.Xml;
using System.Configuration;
using Microsoft.Practices.EnterpriseLibrary.Data;
using Microsoft.Practices.EnterpriseLibrary.Data.Sql;

namespace STKMIS.DAL
{
    /// <summary>
    /// 数据操作类（支持泛型操作）
    /// 
    /// </summary>
    public abstract class SqlDBServer
    {

        public const string SqlConnStringModelDb = "SqlConnectionName";
        /// <summary>添加参数</summary>
        /// <param name="db">Database</param>
        /// <param name="dbCommand">DbCommand</param>
        /// <param name="parameters"><![CDATA[List<SercParameter> 键值对形式的参数]]></param>
        private static void Addparameter(Database db, DbCommand dbCommand, List<SqlDBParameter> parameters)
        {
            foreach (SqlDBParameter parameter in parameters)
            {
                db.AddParameter(dbCommand, parameter.Name, parameter.DbType, parameter.Size,
                    parameter.Direction, true, 0, 0, string.Empty, DataRowVersion.Default, parameter.Value);
            }
        }

        /// <summary>设置输出参数值</summary>
        /// <param name="db">Database</param>
        /// <param name="dbCommand">DbCommand</param>
        /// <param name="parameters"><![CDATA[SortedList<string,SercParameter>形式的参数集-->]]></param>
        private static void FillOutParameter(Database db, DbCommand dbCommand, List<SqlDBParameter> parameters)
        {
            foreach (SqlDBParameter parameter in parameters)
            {
                if (parameter.Direction != ParameterDirection.Input)
                {
                    parameter.Value = db.GetParameterValue(dbCommand, parameter.Name);
                }
            }
        }

        #region 普通操作
        /// <summary>获取DataReader形式的记录集</summary>
        /// <param name="sqlCommand">sql语句查询语句</param>
        /// <param name="parameters"><![CDATA[List<SercParameter>键值对的参数]]></param>
        /// <param name="dataBaseName">数据库连接名</param>
        /// <returns>DataReader记录集</returns>
        /// <example>下面是调用本方法的实例
        /// <code>
        /// string sqlCommd = "SELECT * FROM UserInfo WHERE UserName=:UserName";
        /// 
        /// <![CDATA[SortedList<string,SercParameter>parameters = new SortedList<string,SercParameter>();]]>
        /// parameters.Add("@UserName", new SercParameter("@UserName", DbType.String, "张三"));
        /// 
        /// using (IDataReader dr = DataHelper.GetDataReader(sqlCommd, parameters))
        /// {
        ///    while (dr.Read())
        ///     {
        ///        // 对查出来的记录进行处理
        ///     }
        ///     dr.Close();
        /// }
        /// </code>
        /// </example>
        public static IDataReader GetDataReader(string sqlCommand, List<SqlDBParameter> parameters, string dataBaseName)
        {
            IDataReader dataReader = null;
            Database db = null;
            try
            {
                if (dataBaseName != null)
                {
                    db = DatabaseFactory.CreateDatabase(dataBaseName);
                }
                else
                {
                    db = DatabaseFactory.CreateDatabase();
                }
                DbCommand dbCommand = null;
                dbCommand = db.GetSqlStringCommand(sqlCommand);
                if (parameters != null)
                {
                    Addparameter(db, dbCommand, parameters);
                }
                dbCommand.CommandTimeout = 60;
                dataReader = db.ExecuteReader(dbCommand);
                if (parameters != null)
                {
                    FillOutParameter(db, dbCommand, parameters);
                }
            }
            catch (Exception ex)
            {
                 throw(ex);
            }
            return dataReader;
        }

        /// <summary>获取DataReader形式的记录集</summary>
        /// <param name="sqlCommand">sql语句查询语句</param>
        /// <param name="dataBaseName">数据库连接名</param>
        /// <returns>DataReader记录集</returns>
        /// <example> 下面是调用本方法的实例
        /// <code>
        /// string sqlCommd = "SELECT * FROM UserInfo";
        /// using (IDataReader dr = DataHelper.GetDataReader(sqlCommd))
        /// {
        ///    while (dr.Read())
        ///    {
        ///       // 对查出来的记录进行处理
        ///    }
        ///    dr.Close();
        /// }
        /// </code>
        /// </example>
        public static IDataReader GetDataReader(string sqlCommand)
        {
            return GetDataReader(sqlCommand, null);
        }

        /// <summary>获取DataReader形式的记录集</summary>
        /// <param name="sqlCommand">sql语句查询语句</param>
        /// <param name="dataBaseName">对应数据库实例名</param>
        /// <returns></returns>
        /// <example> 下面是调用本方法的实例
        /// <code>
        /// string sqlCommd = "SELECT * FROM UserInfo";
        /// using (IDataReader dr = DataHelper.GetDataReader(sqlCommd,dataBaseName))
        /// {
        ///    while (dr.Read())
        ///    {
        ///       // 对查出来的记录进行处理
        ///    }
        ///    dr.Close();
        /// }
        /// </code>
        /// </example>
        public static IDataReader GetDataReader(string sqlCommand, string dataBaseName)
        {
            return GetDataReader(sqlCommand, null, dataBaseName);
        }


       

        /// <summary>获取DataSet</summary>
        /// <param name="sqlCommand">sql语句查询语句</param>
        /// <returns>DataSet</returns>
        /// <example>下面是调用本方法的实例
        /// <code>
        /// string sqlCommd = "SELECT * FROM UserInfo'";
        /// DataSet ds = DataHelper.GetDataSet(sqlCommd);
        /// </code>
        /// </example>
        public static DataSet QueryDataSet(string sqlCommand)
        {
            return QueryDataSet(sqlCommand, null);
        }

        /// <summary>获取DataSet</summary>
        /// <param name="sqlCommand">sql语句查询语句</param>
        /// <param name="dataBaseName">数据库连接名</param>
        /// <returns>DataSet</returns>
        /// <example>下面是调用本方法的实例
        /// <code>
        /// string sqlCommd = "SELECT * FROM UserInfo'";
        /// DataSet ds = DataHelper.GetDataSet(sqlCommd,dataBaseName);
        /// </code>
        /// </example>
        public static DataSet QueryDataSet(string sqlCommand, string dataBaseName)
        {
            return QueryDataSet(sqlCommand, null, dataBaseName);
        }
       
        ///  <summary>获取DataSet</summary>
        /// <param name="sqlCommand">sql语句查询语句</param>
        /// <param name="parameters"><![CDATA[SortedList<string,SercParameter>键值对的参数列表]]></param>
        /// <param name="dataBaseName">数据库连接名</param>
        /// <returns>DataSet</returns>
        /// <example> 下面是调用本方法的实例
        /// <code>
        /// string sqlCommd = "SELECT * FROM UserInfo WHERE UserName=@UserName";
        ///
        /// <![CDATA[SortedList<string,SercParameter> parameter = new SortedList<string,SercParameter>();]]>
        /// parameter.Add("@UserName", new SercParameter("@UserName", DbType.String, "张三"));
        ///
        /// DataSet ds = new DataSet();
        /// ds = DataHelper.GetDataSet(sqlCommd, parameter);
        /// </code>
        /// </example>
        public static DataSet QueryDataSet(string sqlCommand, List<SqlDBParameter> parameters, string dataBaseName)
        {
            DataSet ds = new DataSet();
            Database db = null;
            try
            {
                if (dataBaseName != null)
                {
                    db = DatabaseFactory.CreateDatabase(dataBaseName);
                }
                else
                {
                    db = DatabaseFactory.CreateDatabase();
                }
                DbCommand dbCommand = db.GetSqlStringCommand(sqlCommand);
                if (parameters != null)
                {
                    Addparameter(db, dbCommand, parameters);
                }
                dbCommand.CommandTimeout = 60;
                ds = db.ExecuteDataSet(dbCommand);
                if (parameters != null)
                {
                    FillOutParameter(db, dbCommand, parameters);
                }
            }
            catch (Exception ex)
            {
                 throw(ex);
            }
            return ds;
        }
        public static DataSet QueryDataSetWithTableNames(string sqlCommand, string dataBaseName, List<string> tableNames)
        {
            return QueryDataSetWithTableNames(sqlCommand, dataBaseName, null, tableNames);
        }
        public static DataSet QueryDataSetWithTableNames(string sqlCommand, string dataBaseName, List<SqlDBParameter> parameters, List<string> tableNames)
        {
            DataSet ds = new DataSet();
            Database db = null;
            try
            {
                if (dataBaseName != null)
                {
                    db = DatabaseFactory.CreateDatabase(dataBaseName);
                }
                else
                {
                    db = DatabaseFactory.CreateDatabase();
                }
                DbCommand dbCommand = db.GetSqlStringCommand(sqlCommand);
                dbCommand.CommandTimeout = 60;
                if (parameters != null)
                {
                    Addparameter(db, dbCommand, parameters);
                }
                 //补充的
                dbCommand.Connection = db.CreateConnection();
                DbDataAdapter adapter =  db.GetDataAdapter();
                adapter.SelectCommand = dbCommand;
                if (dbCommand.CommandText.IndexOf(';') != -1)
                {
                    for (int i = 0; i < tableNames.Count; i++)
                    {
                        if (i == 0)
                        {
                            adapter.TableMappings.Add("Table", tableNames[i]);
                        }
                        else
                        {
                            adapter.TableMappings.Add("Table" + i.ToString(), tableNames[i]);
                        }
                    }
                }
               
                adapter.Fill(ds);
                 //补充结束
                //ds = db.ExecuteDataSet(dbCommand);
                if (parameters != null)
                {
                    FillOutParameter(db, dbCommand, parameters);
                }
            }
            catch (Exception ex)
            {
                 throw(ex);
            }
            return ds;
        }
      


        /// <summary> 获取DataTable</summary>
        /// <param name="sqlCommand">sql语句查询语句</param>
        /// <param name="parameters">参数</param>
        /// <param name="dataBaseName">数据库连接名</param>
        /// <returns></returns>
        public static DataTable QueryDataTable(string sqlCommand, List<SqlDBParameter> parameters, string dataBaseName)
        {
            DataTable dt = new DataTable();
            Database db = null;
            try
            {
                if (dataBaseName != null)
                {
                    db = DatabaseFactory.CreateDatabase(dataBaseName);
                }
                else
                {
                    db = DatabaseFactory.CreateDatabase();
                }
                DbCommand dbCommand = null;
                dbCommand = db.GetSqlStringCommand(sqlCommand);
                if (parameters != null)
                {
                    Addparameter(db, dbCommand, parameters);
                }
                dbCommand.CommandTimeout = 60;
                dt = db.ExecuteDataSet(dbCommand).Tables[0];
                if (parameters != null)
                {
                    FillOutParameter(db, dbCommand, parameters);
                } 
            }
            catch (Exception ex)
            {
                 throw(ex);
            }
            return dt;
        }

        /// <summary> 获取DataTable</summary>
        /// <param name="sqlCommand">sql语句查询语句</param>
        /// <param name="dataBaseName">数据库连接名</param>
        /// <returns></returns>
        public static DataTable QueryDataTable(string sqlCommand, string dataBaseName)
        {
            return QueryDataTable(sqlCommand, null, dataBaseName);
        }

        // <summary> 获取DataTable</summary>
        /// <param name="sqlCommand">sql语句查询语句</param>
        /// <param name="dataBaseName">数据库连接名</param>
        /// <returns></returns>
        public static DataTable QueryDataTable(string sqlCommand)
        {
            return QueryDataTable(sqlCommand, null, null);
        }

        /// <summary>获取首行首列</summary>
        /// <param name="sqlCommand">sql语句查询语句</param>
        /// <param name="parameters"><![CDATA[SortedList<string,SercParameter>键值对参数集合]]></param>
        /// <param name="transaction">事务</param>
        /// <param name="dataBaseName">数据库连接名</param>
        /// <returns>获取首行首列</returns>
        /// <example> 下面是调用本方法的实例
        /// <code>
        /// string sqlCommd = "SELECT * FROM UserInfo WHERE UserName=@UserName";
        /// 
        /// <![CDATA[SortedList<string,SercParameter> parameter = new SortedList<string,SercParameter>();]]>
        /// parameter.Add("@UserName", new SercParameter("@UserName", DbType.String, "张三"));
        /// 
        /// string name = DataHelper.GetScalar(sqlCommd, parameter).ToString();
        /// </code>
        /// </example>
        public static object QuerySingleValue(string sqlCommand, List<SqlDBParameter> parameters, DbTransaction transaction, string dataBaseName)
        {
            object value = null;
            Database db = null;
            try
            {
                if (dataBaseName != null)
                {
                    db = DatabaseFactory.CreateDatabase(dataBaseName);
                }
                else
                {
                    db = DatabaseFactory.CreateDatabase();
                }
                DbCommand dbCommand = null;
                dbCommand = db.GetSqlStringCommand(sqlCommand);
                dbCommand.CommandTimeout = 60;
                if (parameters != null)
                {
                    Addparameter(db, dbCommand, parameters);
                }
                if (transaction != null)
                {
                    value = db.ExecuteScalar(dbCommand, transaction);
                }
                else
                {
                    value = db.ExecuteScalar(dbCommand);
                }
                if (parameters != null)
                {
                    FillOutParameter(db, dbCommand, parameters);
                }
            }
            catch (Exception ex)
            {
                 throw(ex);
            }
            return value;
        }

        /// <summary>获取首行首列</summary>
        /// <param name="sqlCommand">sql语句查询语句</param>
        /// <param name="parameters"><![CDATA[SortedList<string,SercParameter>键值对参数集合]]></param>
        /// <param name="dataBaseName">数据库连接名</param>
        /// <returns>返回结果</returns>
        public static object QuerySingleValue(string sqlCommand, List<SqlDBParameter> parameters, string dataBaseName)
        {
            return QuerySingleValue(sqlCommand, parameters, null, dataBaseName);
        }

        /// <summary>获取首行首列</summary>
        /// <param name="sqlCommand">sql语句查询语句</param>
        /// <param name="dataBaseName">数据库连接名</param>
        /// <param name="transaction">事务</param>
        /// <returns>返回结果</returns>
        public static object QuerySingleValue(string sqlCommand, DbTransaction transaction, string dataBaseName)
        {
            return QuerySingleValue(sqlCommand, null, transaction, dataBaseName);
        }

        /// <summary>获取首行首列</summary>
        /// <param name="sqlCommand">sql语句查询语句</param>
        /// <returns>获取首行首列</returns>
        /// <example> 下面是调用本方法的实例
        /// <code>
        /// string sqlCommd = "SELECT UserName FROM UserInfo";
        /// string name = DataHelper.GetScalar(sqlCommd).ToString();
        /// </code>
        /// </example>
        public static object QuerySingleValue(string sqlCommand)
        {
            return QuerySingleValue(sqlCommand, null, null, null);
        }

        /// <summary>获取首行首列</summary>
        /// <param name="sqlCommand">sql语句查询语句</param>
        /// <param name="dataBaseName">数据库连接名</param>
        /// <returns>获取首行首列</returns>
        public static object QuerySingleValue(string sqlCommand, string dataBaseName)
        {
            return QuerySingleValue(sqlCommand, null, null, dataBaseName);
        }

        /// <summary>传入sql语句返回影响记录数</summary>
        /// <param name="sqlCommand">sql语句</param>
        /// <param name="parameters"><![CDATA[SortedList<string,SercParameter>键值对形式参数]]></param>
        /// <param name="transaction">事务</param>
        /// <param name="dataBaseName">数据库连接名</param>
        /// <returns>影响记录数</returns>
        /// <example>　下面是调用本方法的实例
        /// <code>
        /// //删除
        /// <![CDATA[SortedList<string,SercParameter> paramDelete = new SortedList<string,SercParameter>();]]>
        /// paramDelete.Add("@userID", new SercParameter(":userID", DbType.Int32, 8));
        /// string sqlComdDelete = "DELETE FROM  UserInfo WHERE UserID=@userID";
        /// 
        /// //插入
        /// <![CDATA[SortedList<string,SercParameter> paramInsert = new SortedList<string,SercParameter>();]]>
        /// paramInsert.Add("@userID", new SercParameter("@userID", DbType.Int32, 10));
        /// paramInsert.Add("@userName", new SercParameter("@userName", DbType.String, "王五"));
        /// string sqlComdInsert = "INSERT INTO UserInfo (UserID, UserName) VALUES (@userID,@userName)";
        /// 
        /// using (SercTransaction transaction = new SercTransaction()) //创建事务
        /// {
        ///     transaction.BeginTransaction();//开启事务
        ///    try
        ///    {
        ///        DataHelper.ExecuteNonQuery(sqlComdDelete, paramDelete, transaction.Transaction);
        ///        DataHelper.ExecuteNonQuery(sqlComdInsert, paramInsert, transaction.Transaction);
        ///        transaction.Commit();//提交事务
        ///    }
        ///    catch
        ///    {
        ///        transaction.Rollback();//回滚事务
        ///    }
        /// }
        /// </code>
        /// </example>
        public static int Execute(string sqlCommand, List<SqlDBParameter> parameters, DbTransaction transaction, string dataBaseName)
        {
            int affectRows = 0;
            Database db = null;
            try
            {
                if (dataBaseName != null)
                {
                    db = DatabaseFactory.CreateDatabase(dataBaseName);
                }
                else
                {
                    db = DatabaseFactory.CreateDatabase();
                }
                DbCommand dbCommand = null;
                dbCommand = db.GetSqlStringCommand(sqlCommand);
                if (parameters != null)
                {
                    Addparameter(db, dbCommand, parameters);
                }
                dbCommand.CommandTimeout = 60;
                if (transaction != null)
                {
                    affectRows = db.ExecuteNonQuery(dbCommand, transaction);
                }
                else
                {
                    affectRows = db.ExecuteNonQuery(dbCommand);
                }
                if (parameters != null)
                {
                    FillOutParameter(db, dbCommand, parameters);
                }
            }
            catch (Exception ex)
            {
                 throw(ex);
            }
            return affectRows;
        }

        /// <summary>传入sql语句返回影响记录数</summary>
        /// <param name="sqlCommand">sql语句</param>
        /// <param name="parameters"><![CDATA[SortedList<string,SercParameter>参数列表]]></param>
        /// <param name="dataBaseName">数据库连接名</param>
        /// <returns>影响记录数</returns>
        /// <example> 下面是调用本方法的实例 插入数据
        /// <code>
        /// //插入
        /// string sqlComdInsert = "INSERT INTO UserInfo (UserID, UserName) VALUES (:userID,:userName)";
        /// <![CDATA[SortedList<string,SercParameter> paramInsert = new SortedList<string,SercParameter>();]]>
        /// paramInsert.Add("@userID", new SercParameter("@userID", DbType.Int32, 18));
        /// paramInsert.Add("@userName", new SercParameter("@userName", DbType.String, "李六"));
        /// DataHelper.ExecuteNonQuery(sqlComdInsert, paramInsert);
        /// </code>
        /// </example>
        public static int Execute(string sqlCommand, List<SqlDBParameter> parameters, string dataBaseName)
        {
            return Execute(sqlCommand, parameters, null, dataBaseName);
        }

        /// <summary>传入sql语句返回影响记录数</summary>
        /// <param name="sqlCommand">sql语句</param>
        /// <param name="transaction">事务</param>
        /// <param name="dataBaseName">数据库连接名</param>
        /// <returns>影响记录数</returns>
        /// <example> 下面是调用本方法的实例
        /// <code>
        /// string sqlComdDelete1 = "DELETE FROM  UserInfo WHERE UserID=1";
        /// string sqlComdDelete2 = "DELETE FROM  UserInfo WHERE UserID=2";
        /// using (SercTransaction transaction = new SercTransaction()) //创建事务
        /// {
        ///    transaction.BeginTransaction();//开启事务
        ///    try
        ///    {
        ///        DataHelper.ExecuteNonQuery(sqlComdDelete1, transaction.Transaction);
        ///        DataHelper.ExecuteNonQuery(sqlComdDelete2, transaction.Transaction);
        ///        transaction.Commit();//提交事务
        ///    }
        ///    catch
        ///    {
        ///        transaction.Rollback();//回滚事务
        ///    }
        ///
        /// }
        /// </code>
        /// </example>
        public static int Execute(string sqlCommand, DbTransaction transaction, string dataBaseName)
        {
            return Execute(sqlCommand, null, transaction, dataBaseName);
        }

        /// <summary>传入sql语句返回影响记录数</summary>
        /// <param name="sqlCommand">sql语句</param>
        /// <returns>影响记录数</returns>
        /// <example>
        /// <code>
        /// string sqlComdDelete = "DELETE FROM  UserInfo WHERE UserID=8";
        /// DataHelper.ExecuteNonQuery(sqlComdDelete);
        /// </code>
        /// </example>
        public static int Execute(string sqlCommand)
        {
            return Execute(sqlCommand, null, null, null);
        }

        /// <summary>传入sql语句返回影响记录数</summary>
        /// <param name="sqlCommand">sql语句</param>
        /// <param name="dataBaseName">数据库连接名</param>
        /// <returns>影响记录数</returns>
        /// <example>
        /// <code>
        /// string sqlComdDelete = "DELETE FROM  UserInfo WHERE UserID=8";
        /// DataHelper.ExecuteNonQuery(sqlComdDelete);
        /// </code>
        /// </example>
        public static int Execute(string sqlCommand, string dataBaseName)
        {
            return Execute(sqlCommand, null, null, dataBaseName);
        }
        #endregion

        #region 存储过程操作

        /// <summary>调用存储过程获取DataReader必须和存储过程中的输出参数相同。</summary>
        /// <param name="sqlStoredProcName">存储过程名</param>
        /// <param name="parameters"><![CDATA[SortedList<string,SercParameter>参数列表]]></param>
        /// <returns>DataReader</returns>
        /// <example> 下面是调用本方法的实例
        /// <code>
        /// <![CDATA[SortedList<string,SercParameter> parameters = new SortedList<string,SercParameter>();]]>
        /// //否则，不会成功返回DataRead记录。这是Oracle存储过程的特性返回记录集必须以游标的形式返回，请大家注意。
        /// using (IDataReader dr = DataHelper.GetDataReaderByStoredProc("GetRecord", parameters))
        /// {
        ///    dr.Read();
        ///    this.TextBox1.Text = dr["UserName"].ToString();
        ///    dr.Close();
        /// }
        /// </code>
        /// </example>
        public static IDataReader GetDataReaderByStoredProc(string sqlStoredProcName, List<SqlDBParameter> parameters, string dataBaseName)
        {
            IDataReader dataReader = null;
            Database db = null;
            try
            {
                if (dataBaseName != null)
                {
                    db = DatabaseFactory.CreateDatabase(dataBaseName);
                }
                else
                {
                    db = DatabaseFactory.CreateDatabase();
                }
                DbCommand dbCommand = null;
                dbCommand = db.GetStoredProcCommand(sqlStoredProcName);
                dbCommand.CommandTimeout = 60;
                if (parameters != null)
                {
                    Addparameter(db, dbCommand, parameters);
                }

                dataReader = db.ExecuteReader(dbCommand);
                if (parameters != null)
                {
                    FillOutParameter(db, dbCommand, parameters);
                }
            }
            catch (Exception ex)
            {
                 throw(ex);
            }
            return dataReader;
        }
        public static int ExecuteStoredProc(string sqlStoredProcName, List<SqlDBParameter> parameters, DbTransaction transaction, string dataBaseName)
        {
            int executedRows = 0;
            Database db = null;
            try
            {
                if (dataBaseName != null)
                {
                    db = DatabaseFactory.CreateDatabase(dataBaseName);
                }
                else
                {
                    db = DatabaseFactory.CreateDatabase();
                }
                DbCommand dbCommand = null;
                dbCommand = db.GetStoredProcCommand(sqlStoredProcName);
                dbCommand.CommandTimeout = 60;
                if (parameters != null)
                {
                    Addparameter(db, dbCommand, parameters);
                }

                if (transaction != null)
                {
                    executedRows = db.ExecuteNonQuery(dbCommand, transaction);
                }
                else
                {
                    executedRows = db.ExecuteNonQuery(dbCommand);
                }

                if (parameters != null)
                {
                    FillOutParameter(db, dbCommand, parameters);
                }
               
            }
            catch (Exception ex)
            {
                 throw(ex);
            }
            return executedRows;
        }
        public static int ExecuteStoredProc(string sqlStoredProcName, List<SqlDBParameter> parameters, string dataBaseName)
        {
            return ExecuteStoredProc(sqlStoredProcName,parameters,null,dataBaseName);
        }
        /// <summary>调用存储过程获取DataReader,</summary>
        /// <param name="sqlStoredProcName">存储过程名</param>
        /// <returns>DataReader</returns>
        public static IDataReader GetDataReaderByStoredProc(string sqlStoredProcName, string dataBaseName)
        {
            return GetDataReaderByStoredProc(sqlStoredProcName, null, dataBaseName);
        }

        /// <summary>调用存储过程获取XmlReader(此方法只对Sqlserver有效)</summary>
        /// <param name="sqlStoredProcName">存储过程名</param>
        /// <param name="parameters">参数</param>
        /// <returns>XmlReader</returns>
        public static XmlReader GetXmlReaderByStoredProc(string sqlStoredProcName, List<SqlDBParameter> parameters)
        {
            SqlDatabase dbSQL = DatabaseFactory.CreateDatabase() as SqlDatabase;
            DbCommand dbCommand = dbSQL.GetStoredProcCommand(sqlStoredProcName);

            XmlReader xmlReader = null;
            if (parameters != null)
            {
                Addparameter(dbSQL, dbCommand, parameters);
            }
            xmlReader = dbSQL.ExecuteXmlReader(dbCommand);
            if (parameters != null)
            {
                FillOutParameter(dbSQL, dbCommand, parameters);
            }
            return xmlReader;
        }

        /// <summary>调用存储过程获取XmlReader(此方法只对Sqlserver有效)</summary>
        /// <param name="sqlStoredProcName">存储过程名</param>
        /// <returns>XmlReader</returns>
        public static XmlReader GetXmlReaderByStoredProc(string sqlStoredProcName)
        {
            return GetXmlReaderByStoredProc(sqlStoredProcName, null);
        }

        /// <summary>调用存储过程获取DataSet  必须和存储过程中的输出参数相同。</summary>
        /// <param name="sqlStoredProcName">存储过程名</param>
        /// <param name="parameters"><![CDATA[SortedList<string,SercParameter>参数列表]]></param>
        /// <param name="dataBaseType">数据库类型</param>
        /// <returns>DataSet</returns>
        /// <example>
        /// <code>
        /// <![CDATA[SortedList<string,SercParameter> parameters = new SortedList<string,SercParameter>();]]>
        /// </code>
        /// </example>
        public static DataSet GetDataSetByStoredProc(string sqlStoredProcName, List<SqlDBParameter> parameters, string dataBaseName)
        {
            DataSet ds = new DataSet();
            Database db = null;
            try
            {
                if (dataBaseName != null)
                {
                    db = DatabaseFactory.CreateDatabase(dataBaseName);
                }
                else
                {
                    db = DatabaseFactory.CreateDatabase();
                }
                DbCommand dbCommand = null;
                dbCommand = db.GetStoredProcCommand(sqlStoredProcName);
                dbCommand.CommandTimeout = 60;
                if (parameters != null)
                {
                    Addparameter(db, dbCommand, parameters);
                }
                ds = db.ExecuteDataSet(dbCommand);
                if (parameters != null)
                {
                    FillOutParameter(db, dbCommand, parameters);
                }
            }
            catch (Exception ex)
            {
                 throw(ex);
            }
            return ds;
        }


        /// <summary>调用存储过程获取DataSet  必须和存储过程中的输出参数相同。</summary>
        /// <param name="sqlStoredProcName">存储过程名</param>
        /// <param name="parameters"><![CDATA[SortedList<string,SercParameter>参数列表]]></param>
        /// <param name="dataBaseType">数据库类型</param>
        /// <returns>DataSet</returns>
        /// <example>
        /// <code>
        /// <![CDATA[SortedList<string,SercParameter> parameters = new SortedList<string,SercParameter>();]]>
        /// </code>
        /// </example>
        public static DataTable GetDataTableByStoredProc(string sqlStoredProcName, List<SqlDBParameter> parameters, string dataBaseName)
        {
            DataSet ds = new DataSet();
            Database db = null;
            try
            {
                if (dataBaseName != null)
                {
                    db = DatabaseFactory.CreateDatabase(dataBaseName);
                }
                else
                {
                    db = DatabaseFactory.CreateDatabase();
                }
                DbCommand dbCommand = null;
                dbCommand = db.GetStoredProcCommand(sqlStoredProcName);
                dbCommand.CommandTimeout = 60;
                if (parameters != null)
                {
                    Addparameter(db, dbCommand, parameters);
                }
                ds = db.ExecuteDataSet(dbCommand);
                if (parameters != null)
                {
                    FillOutParameter(db, dbCommand, parameters);
                }
            }
            catch (Exception ex)
            {
                throw (ex);
            }

            if (ds != null && ds.Tables.Count > 0)
            {
                return ds.Tables[0];
            }
            else
            {
                return null;
            }
        }

        public static object GetSingleValueByStoredProc(string sqlStoredProcName, List<SqlDBParameter> parameters, DbTransaction transaction, string dataBaseName)
        {
            object returnValue = null;
            Database db = null;
            try
            {
                if (dataBaseName != null)
                {
                    db = DatabaseFactory.CreateDatabase(dataBaseName);
                }
                else
                {
                    db = DatabaseFactory.CreateDatabase();
                }
                DbCommand dbCommand = null;
                dbCommand = db.GetStoredProcCommand(sqlStoredProcName);
                dbCommand.CommandTimeout = 60;
                if (parameters != null)
                {
                    Addparameter(db, dbCommand, parameters);
                }

                if (transaction != null)
                {
                    returnValue = db.ExecuteScalar(dbCommand, transaction);
                }
                else
                {
                    returnValue = db.ExecuteScalar(dbCommand);
                }

                if (parameters != null)
                {
                    FillOutParameter(db, dbCommand, parameters);
                }
            }
            catch (Exception ex)
            {
                 throw(ex);
            }
            return returnValue;
        }

        public static object GetSingleValueByStoredProc(string sqlStoredProcName, List<SqlDBParameter> parameters,string dataBaseName)
        {
            return GetSingleValueByStoredProc(sqlStoredProcName, parameters, null, dataBaseName);
        }



        #endregion


        #region 查询数据处理,得到List<T>对象集合

        #region 根据SqlReader得到指定类型对象的列表(内部用)
        /// 根据SqlReader得到指定类型对象的列表
        /// </summary>
        /// <typeparam name="T">自定义类型</typeparam>
        /// <param name="rd">要读取的SqlReader</param>
        /// <returns>指定类型的对象列表</returns>
        private static List<T> GetObjectList<T>(IDataReader rd, string dataBaseName) where T : new()
        {
            //SqlConn = new SqlConnection(connectionString);
            Database db = null;
            if (dataBaseName != null)
            {
                db = DatabaseFactory.CreateDatabase(dataBaseName);
            }
            else
            {
                db = DatabaseFactory.CreateDatabase();
            }
            List<T> list = new List<T>();
            if (rd != null)
            {
                while (rd.Read())
                {
                    T model = new T();
                    System.ComponentModel.PropertyDescriptorCollection props = System.ComponentModel.TypeDescriptor.GetProperties(model.GetType());
                    System.ComponentModel.PropertyDescriptor prop = null;
                    for (int i = 0; i < rd.FieldCount; i++)
                    {
                        string pName = rd.GetName(i);
                        prop = props.Find(pName.Trim(), true);
                        if (prop != null && (object)rd[i] != System.DBNull.Value)
                            prop.SetValue(model, (object)rd[i]);
                    }

                    list.Add(model);
                }
            }
            return list;
        }
        #endregion

        private static T GetObjectItem<T>(IDataReader rd, string dataBaseName) where T : new()
        {
            //SqlConn = new SqlConnection(connectionString);
            Database db = null;
            if (dataBaseName != null)
            {
                db = DatabaseFactory.CreateDatabase(dataBaseName);
            }
            else
            {
                db = DatabaseFactory.CreateDatabase();
            }
            T model = new T();
            if (rd != null)
            {
                while (rd.Read())
                {
                    System.ComponentModel.PropertyDescriptorCollection props = System.ComponentModel.TypeDescriptor.GetProperties(model.GetType());
                    System.ComponentModel.PropertyDescriptor prop = null;
                    for (int i = 0; i < rd.FieldCount; i++)
                    {
                        string pName = rd.GetName(i);
                        prop = props.Find(pName.Trim(), true);
                        if (prop != null && (object)rd[i] != System.DBNull.Value)
                            prop.SetValue(model, (object)rd[i]);
                    }
                }
            }
            return model;
        }

        #region 根据Sql语句得到指定类型的对象列表信息
        /// <summary>
        /// 根据Sql语句得到指定类型的对象列表信息
        /// </summary>
        /// <typeparam name="T">自定义类型</typeparam>
        /// <param name="StrSql">要执行的Sql语句</param>         
        /// <returns>指定类型的对象列表</returns>
        public static List<T> GetListBySqlStr<T>(string sqlCommand, string dataBaseName) where T : new()
        {
            Database db = null;
            if (dataBaseName != null)
            {
                db = DatabaseFactory.CreateDatabase(dataBaseName);
            }
            else
            {
                db = DatabaseFactory.CreateDatabase();
            }
            IDataReader rd = null;
            try
            {
                DbCommand dbCommand = null;
                dbCommand = db.GetSqlStringCommand(sqlCommand);
                dbCommand.CommandTimeout = 60;
                rd = db.ExecuteReader(dbCommand);
                return GetObjectList<T>(rd, dataBaseName);
            }
            catch (Exception ex)
            {
                throw ex;
            }
           
        }
        #endregion

        public static T GetItemBySqlStr<T>(string sqlCommand, string dataBaseName) where T : new()
        {
            Database db = null;
            if (dataBaseName != null)
            {
                db = DatabaseFactory.CreateDatabase(dataBaseName);
            }
            else
            {
                db = DatabaseFactory.CreateDatabase();
            }
            IDataReader rd = null;
            try
            {
                DbCommand dbCommand = null;
                dbCommand = db.GetSqlStringCommand(sqlCommand);
                dbCommand.CommandTimeout = 60;
                rd = db.ExecuteReader(dbCommand);
                return GetObjectItem<T>(rd, dataBaseName);
            }
            catch (Exception ex)
            {
                throw ex;
            }
        }


        #region 根据存储过程名称得到指定类型的对象列表信息
        /// <summary>
        /// 根据存储过程名称得到指定类型的对象列表信息
        /// </summary>
        /// <typeparam name="T">自定义类型</typeparam>
        /// <param name="ProcName">存储过程名称</param>
        /// <param name="paras">存储过程参数</param>
        /// <returns>指定类型的对象列表</returns>
        public static List<T> GetListByProc<T>(string ProcName, List<SqlDBParameter> parameters, string dataBaseName) where T : new()
        {
            Database db = null;
            if (dataBaseName != null)
            {
                db = DatabaseFactory.CreateDatabase(dataBaseName);
            }
            else
            {
                db = DatabaseFactory.CreateDatabase();
            }
            IDataReader rd = null;
            try
            {
                DbCommand dbCommand = null;
                dbCommand = db.GetStoredProcCommand(ProcName);
                dbCommand.CommandTimeout = 60;
                if (parameters != null)
                {
                    AddSqlParams(dbCommand, parameters);
                }
                rd = db.ExecuteReader(dbCommand);
                return GetObjectList<T>(rd, dataBaseName);
            }
            catch (Exception ex)
            {
                throw ex;
            }

        }
        #endregion

        #region 添加Parameters过程(内部用)
        /// <summary>
        /// 向指定的SqlCommand里添加指定的参数
        /// </summary>
        /// <param name="SqlComm">被添加的命令</param>
        /// <param name="Params">要添加的参数</param>
        private static void AddSqlParams(DbCommand SqlComm, List<SqlDBParameter> Params)
        {
            try
            {
                foreach (SqlDBParameter Param in Params)
                {
                    SqlComm.Parameters.Add(Param);
                }
            }
            catch (Exception ex)
            {
                throw ex;
            }
        }
        #endregion
        #endregion
    }
}
